﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

using System.Data.SqlClient;
using System.Data;

namespace Utilities
{
    public static class ExtrensionsDataSQL
    {
        /// <summary>
        /// Close the especified SqlDataReader
        /// </summary>
        /// <param name="reader">SqlDataReader to close</param>
        public static void me_CloseReader(this SqlDataReader reader)
        {
            if (reader != null)
            {
                reader.Dispose();
            }
        }

        /// <summary>
        /// Add a parameter to a SqlCommand query
        /// </summary>
        /// <typeparam name="T">abstract value to be passed</typeparam>
        /// <param name="query">SqlCommand object that the parameter is going to be attached to</param>
        /// <param name="column">name of the column</param>
        /// <param name="value">value of the parameter to add to the column</param>
        /// <param name="type">type SqlDbType of the value that is being passed</param>
        public static void me_AddParameterToQuery<T>(this SqlCommand query, string column, T value, SqlDbType type)
        {
            SqlParameter parameter = new SqlParameter(column, type);
            parameter.Value = value;
            query.Parameters.Add(parameter);
        }

        /// <summary>
        /// Add Output parameter to a SqlCommand query
        /// </summary>
        /// <param name="query">SqlCommand object that the parameter is going to be attached to</param>
        /// <param name="column">name of the variable</param>
        /// <param name="type">type SqlDbType of the value that is being passed</param>
        /// <param name="direction">parameter direction</param>
        public static void me_AddOutputParameterToQuery(this SqlCommand query, string variable, SqlDbType type, ParameterDirection direction)
        {
            SqlParameter parameter = new SqlParameter(variable, type);
            parameter.Direction = direction;
            query.Parameters.Add(parameter);
        }

        /// <summary>
        /// Entension method that allows to retreive the column values as a string or an empty string if column is null.
        /// The column must be of string type
        /// </summary>
        /// <param name="reader">SqlDataReader being used in the connection</param>
        /// <param name="columnName">Column name the we want to get the value from</param>
        /// <returns>returns a string with the column value or a empty string if column is null</returns>
        public static string me_SafeGetString(this SqlDataReader reader, string columnName)
        {
            if (!reader.IsDBNull(reader.GetOrdinal(columnName)))
                return reader.GetString(reader.GetOrdinal(columnName));
            else
                return string.Empty;
        }

        /// <summary>
        /// Entension method that allows to retreive the column values as an Integer or 0 if column is null.
        /// The column must be of int type
        /// </summary>
        /// <param name="command">SqlCommand being used in the connection</param>
        /// <param name="columnName">Column name that we want to get the value from</param>
        /// <returns>returns an Integer with the column value or 0 if column is null</returns>
        public static int me_SafeGetOutputInt(this SqlCommand command, string columnName)
        {
            try
            {
                string value = command.Parameters[columnName].Value.ToString();
                return Int32.Parse(command.Parameters[columnName].Value.ToString());
            }
            catch (Exception) { }

            return 0;
        }

        /// <summary>
        /// Entension method that allows to retreive the column values as an Integer or 0 if column is null.
        /// The column must be of int type
        /// </summary>
        /// <param name="reader">SqlDataReader being used in the connection</param>
        /// <param name="columnName">Column name the we want to get the value from</param>
        /// <returns>returns an Integer with the column value or 0 if column is null</returns>
        public static int me_SafeGetInt(this SqlDataReader reader, string columnName)
        {
            if (!reader.IsDBNull(reader.GetOrdinal(columnName)))
                return reader.GetInt32(reader.GetOrdinal(columnName));
            else
                return 0;
        }

        /// <summary>
        /// Entension method that allows to retreive the column values as a Guid or empty Guid if column is null.
        /// The column must be of Guid type
        /// </summary>
        /// <param name="reader">SqlDataReader being used in the connection</param>
        /// <param name="columnName">Column name the we want to get the value from</param>
        /// <returns>returns an Guid with the column value or empty Guid if column is null</returns>
        public static Guid me_SafeGetGuid(this SqlDataReader reader, string columnName)
        {
            if (!reader.IsDBNull(reader.GetOrdinal(columnName)))
                return reader.GetGuid(reader.GetOrdinal(columnName));
            else
                return Guid.Empty;
        }

        /// <summary>
        /// Entension method that allows to retreive the column values as a double or empty bouble if column is null.
        /// The column must be of double type
        /// </summary>
        /// <param name="reader">SqlDataReader being used in the connection</param>
        /// <param name="columnName">Column name the we want to get the value from</param>
        /// <returns>returns an double with the column value or 0 if column is null</returns>
        public static double me_SafeGetDouble(this SqlDataReader reader, string columnName)
        {
            if (!reader.IsDBNull(reader.GetOrdinal(columnName)))
                return reader.GetDouble(reader.GetOrdinal(columnName));
            else
                return 0;
        }

        /// <summary>
        /// Entension method that allows to retreive the column values as a decimal or empty decimal if column is null.
        /// The column must be of decimal type
        /// </summary>
        /// <param name="reader">SqlDataReader being used in the connection</param>
        /// <param name="columnName">Column name the we want to get the value from</param>
        /// <returns>returns an decimal with the column value or 0 if column is null</returns>
        public static decimal me_SafeGetDecimal(this SqlDataReader reader, string columnName)
        {

            if (!reader.IsDBNull(reader.GetOrdinal(columnName)))
            {
                return reader.GetDecimal(reader.GetOrdinal(columnName));
            }
            else
                return 0;
        }

        /// <summary>
        /// Entension method that allows to retreive the column values as a decimal and convert to double or empty double if column is null.
        /// The column must be of decimal type
        /// </summary>
        /// <param name="reader">SqlDataReader being used in the connection</param>
        /// <param name="columnName">Column name the we want to get the value from</param>
        /// <returns>returns a double with the column value or 0 if column is null</returns>
        public static double me_SafeGetDecimalToDouble(this SqlDataReader reader, string columnName)
        {
            if (!reader.IsDBNull(reader.GetOrdinal(columnName)))
            {
                try
                {
                    string value = reader.GetDecimal(reader.GetOrdinal(columnName)).ToString();
                    return Double.Parse(value);
                }
                catch (Exception) { }
            }
            return 0;
        }

        /// <summary>
        /// Entension method that allows to retreive the column values as a Guid or empty Guid if column is null.
        /// The column must be of Guid type
        /// </summary>
        /// <param name="reader">SqlDataReader being used in the connection</param>
        /// <param name="columnName">Column name the we want to get the value from</param>
        /// <returns>returns an Guid with the column value or empty Guid if column is null</returns>
        public static DateTime me_SafeGetDate(this SqlDataReader reader, string columnName)
        {
            if (!reader.IsDBNull(reader.GetOrdinal(columnName)))
                return reader.GetDateTime(reader.GetOrdinal(columnName));
            else
                return DateTime.MinValue;
        }

        /// <summary>
        /// Entension method that allows to retreive the column values as a Guid or empty Guid if column is null.
        /// The column must be of Guid type
        /// </summary>
        /// <param name="reader">SqlDataReader being used in the connection</param>
        /// <param name="columnName">Column name the we want to get the value from</param>
        /// <returns>returns an Guid with the column value or empty Guid if column is null</returns>
        public static bool me_SafeGetBool(this SqlDataReader reader, string columnName)
        {
            if (!reader.IsDBNull(reader.GetOrdinal(columnName)))
                return reader.GetBoolean(reader.GetOrdinal(columnName));
            else
                return false;
        }
    }
}
